/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package testmysql;

import java.sql.*;
import javax.swing.JOptionPane;

/**
 *
 * @author Ronald
 */
public class MysqlConnect2 {

    public static void main(String[] args) {
        Connection conn = null;
        try {
            String userName = "root";
            String password = "onimusha2";
            String url = "jdbc:mysql://localhost/automotive";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);
            System.out.println("Database connection established\n");
        } catch (Exception e) {
            System.err.println("Cannot connect to database server");
        } finally {
            if (conn != null) {
                try {
                    Statement s = conn.createStatement();
                    int count = 0;
                    String database = JOptionPane.showInputDialog("Enter database name for cars: ");
                    int counta = s.executeUpdate("CREATE DATABASE IF NOT EXIST " + database);
                    System.out.println(counta + " row(s) affacted");
                    System.out.println("Add a car to the database...\n");
                    String make = JOptionPane.showInputDialog("Enter the car make: ");
                    String model = JOptionPane.showInputDialog("Enter the car model: ");
                    String y = JOptionPane.showInputDialog("Enter the car year: ");
                    int year = Integer.parseInt(y);
                    System.out.println("You entered: " + make + " " + model + " " + year + "\n");
                    Statement x = conn.createStatement();
                    int countx;
                    countx = x.executeUpdate(
                            "INSERT INTO cars (make, model, year)"
                            + " VALUES"
                            + "('" + make + "', '" + model + "', '" + year + "')");
                    x.close();
                    System.out.println(countx + " car was succesfully entered into the database\n");
                    System.out.println("Retrieving current database...\n");
                    Statement z = conn.createStatement();
                    z.executeQuery("SELECT * FROM cars");
                    ResultSet rs = z.getResultSet();
                    int countz = 0;
                    while (rs.next()) {
                        int idVal = rs.getInt("id");
                        String makeVal = rs.getString("make");
                        String modelVal = rs.getString("model");
                        int yearVal = rs.getInt("year");
                        System.out.println(
                                "id = " + idVal
                                + ", make = " + makeVal
                                + ", model = " + modelVal
                                + ", year = " + yearVal);
                        ++countz;
                    }
                    rs.close();
                    System.out.println("\nReturn completed");
                    s.close();
                    System.out.println(countz + 1 + " rows were retrieved\n");
                    System.out.println("Select a car to retrieve by it's ID:\n");
                    String i = JOptionPane.showInputDialog("Enter the car to be retrieved ID: ");
                    int id = Integer.parseInt(i);
                    Statement r = conn.createStatement();
                    r.executeQuery("SELECT * FROM cars having id = " + id);
                    ResultSet rx = r.getResultSet();
                    int county = 0;
                    while (rx.next()) {
                        int idVal = rx.getInt("id");
                        String makeVal = rx.getString("make");
                        String modelVal = rx.getString("model");
                        int yearVal = rx.getInt("year");
                        System.out.println(
                                "id = " + idVal
                                + ", make = " + makeVal
                                + ", model = " + modelVal
                                + ", year = " + yearVal);
                        ++county;
                    }
                    rx.close();
                    System.out.println("\nReturn completed");
                    r.close();
                    System.out.println(countz + " rows were retrieved");
                    conn.close();
                    System.out.println("...Database connection terminated");
                } catch (Exception e) {
                    System.out.println("Error in operation.");
                }
            }
        }
    }
    public Connection conn;

    public void Execution() {
        try {
            Statement s = conn.createStatement();
        } catch (SQLException e) {
        }
    }
}
